<?php
class readbatchcard_model extends Model
{
	function readbatchcard_model()
	{
		parent::Model();
		$this->load->database();
	}
  
  function update_batchdetails()
  {
    $result1 = $this->db->query("select batchnum_id,batch_num,total_batch_records from tbl_batchdetails where qc_status=0 and export_status=0");
    $res1 = $result1->result_array();
    foreach($res1 as $batch)
    {
      //$batch['batch_num'];
      $result2 = $this->db->query("select a.*,b.* from tbl_batchtracker as a ,tbl_cafdata as b where a.batch_number='".$batch['batch_num']."' and a.batch_id=b.batch_id and b.caf_qcby != 0");
      $res2 = $result2->num_rows();
      if($batch['total_batch_records'] == $res2)
      {
        $data = array(
               'qc_status' =>'1',
               
            );

      $this->db->where('batchnum_id',$batch['batchnum_id'] );
      $this->db->update('tbl_batchdetails', $data);
        
      }
    }
   
    
  }
 	
	function generate_csv()
	{
		$csv = "";
		 $csv .="MOBILENO~ FILENO~ SNO~ DATE_SUB~ DATE_ENTRY~ DATE_ENTRY_TIME~ USERID~DISNAME~ SIMNO~ PREFIX~ GENDER~ MARITAL~ DOB~ NAME~ FH_NAME~ LOC_ADD1~ LOC_ADD2~ LOC_ADD3~ LOC_CITY~ LOC_PIN~ LOC_PHONE1~ PER_ADD1~ PER_ADD2~ PER_ADD3~ PER_CITY~ PER_PIN~ POI~ POA~ POINO~ POANO~ NATIONALITY~ FLADDR~ RETAILER~ COUNTRY~ SRLNO~ LOC_PHONE2~ ADDTYPE~ CONTACT_PERSON~ BLREG~ SUBSCR_DATE~ LOC_STATE~ PER_STATE~ REF_ADDR~ REF_NAME~ REF_NUMBER~ COMP_DATA_FLAG~ NOP";
		 $csv .="\n";
		 foreach ($_POST as $row)
	      {
	    	$res = $this->db->query("SELECT a.*,b.* FROM `tbl_batchtracker` as a,tbl_cafdata as b where a.batch_number='".$row."' and b.batch_id=a.batch_id");
			$val = $res->result_array();
				foreach($val as $row)
				{
					//print_r($row);exit;
				
					if($row['received_date'])
					{
						$sub_date = $this->required_date($row['received_date']);
						
					}
					
					//data entry time ....
					
					
					if($row['lastupdate'])
					{
						list($entry_date,$time)=explode(" ",$row['lastupdate']);
						$data_entry = $this->required_date($entry_date);
						
					}
					
					if($row['caf_dob'])
					{
						$dob = $this->required_date($row['caf_dob']);
					
					}
					
					
					
						//created function to fetch three different states from db because i found difficult in fetching in single query... 
						
						if($row['caf_doc_state']!=0)
						{
							
							$doc_state = $this->get_state($row['caf_doc_state']);
							
						}
						
						
						//for alternate state...
						
						if($row['caf_alt_state']!=0)
						{
							$alt_state = $this->get_state($row['caf_alt_state']);	
						}
						else
						{
							$alt_state = '';
						}
						
						//for reference state ...
				/*		
						if($row['caf_referer_state']!=0)
						{
							$ref_state = $this->get_state($row['caf_referer_state']);	
						}
						else
						{
							$ref_state = '';
						}
				 * 
				 */
						
						//to fetch area for document address....
						
						if($row['caf_doc_area']!=0)
						{
							$doc_area = $this->get_area($row['caf_doc_area']);	
							
						}
						
						//to fetch area for alternate address
						if($row['caf_alt_area']!=0)
						{
							$alt_area = $this->get_area($row['caf_alt_area']);	
						}
						else
						{
							$alt_area ='';
						}
						
				/*		//to fetch area for reference address
						if($row['caf_referer_area']!=0)
						{
							$ref_area = $this->get_area($row['caf_referer_area']);	
						}
						else
						{
							$ref_area ='';
						}
				 * 
				 */
						
						//to fetch city for document address..
						if($row['caf_doc_city']!=0)
						{
							$doc_city = $this->get_city($row['caf_doc_city']);
						}
						
						//to fetch city for alternate address..
						if($row['caf_alt_city']!=0)
						{
							$alt_city = $this->get_city($row['caf_alt_city']);
						}
						else
						{
							$alt_city ='';
						}
					/*	
						//to fetch city for reference address..
						if($row['caf_referer_city']!=0)
						{
							$ref_city = $this->get_city($row['caf_referer_city']);
						}
						else
						{
							$ref_city ='';
						}
					 * 
					 */
						
						//for local phone if available...to avoid zero from database....
						if($row['caf_doc_phone1']!=0)
						{
							$phone1 = $row['caf_doc_phone1'];
						}
						else
						{
							$phone1 = '';
						}
						
						//for local phone if available...to avoid zero from database....
						if($row['caf_doc_phone2']!=0)
						{
							$phone2 = $row['caf_doc_phone2'];
						}
						else
						{
							$phone2 = '';
						}
						
						
						//for alternate address....
						if($row['caf_alt_pincode']!=0)
						{
							$alt_pincode = $row['caf_alt_pincode'];
						}
						else
						{
							$alt_pincode = '';
						}	
						
						if($row['caf_poitype1'])
						{
							$poi = $this->get_poitype($row['caf_poitype1']);
						}
						
						if($row['caf_poitype1'])
						{
							$poa = $this->get_poitype($row['caf_poitype2']);
						}
						
						
						
						
							//strtoupper								
					$csv .= $row['batch_mobilenum'].'~'.$row['batch_number'].'~'.$row['sl_num'].'~'.$sub_date.'~'.$data_entry.'~'.$data_entry.' '.$time.'~'.'HEALTH'.'~'.
							$row['distributor_name'].'~'.$row['sim_num'].'~'.strtoupper($row['caf_salutation']).'~'.$row['caf_sex'].'~'.
							$row['caf_marital_status'].'~'.$dob.'~'.strtoupper($row['caf_customername']).'~'.strtoupper($row['caf_fathersname']).'~'.strtoupper($row['caf_doc_address1']).
							'~'.strtoupper($row['caf_doc_address2']).'~'.strtoupper($doc_area).'~'.strtoupper($doc_city).'~'.$row['caf_doc_pincode'].'~'.
							$phone1.'~'.strtoupper($row['caf_alt_address1']).'~'.strtoupper($row['caf_alt_address2']).'~'.$alt_area.'~'.$alt_city.'~'.$alt_pincode.'~'.$poi.'~'.$poa.'~'.
							strtoupper($row['caf_poidetails1']).'~'.strtoupper($row['caf_poidetails2']).'~'.strtoupper($row['caf_nationality']).'~'.' '.'~'.strtoupper($row['caf_retailername']).
							'~'.'INDIA'.'~'.' '.'~'.$phone2.'~'.$row['caf_add_type'].'~'.strtoupper($row['cont_person']).'~'.$row['caf_bld_reg'].'~'.$sub_date.'~'.
							$doc_state.'~'.$alt_state.'~'.strtoupper($row['caf_referer_address1']).'~'.'Name'.'~'.'PHONE NUM'.'~'.'Y'.'~'.$row['caf_nop'];
					$csv .="\n";
							
							
							
							
							
							
				}
	      }
		echo $csv;
	} 

  function required_date($var ='')
  {
  	list($yy,$month,$dd) =explode("-",$var);
	//echo $var;exit;
					$sub_date ="";
				   if($month == '1')
                    {
                       $sub_date = $dd.'-JAN-'.$yy;
                    }
					  else if($month == '2')
                    {
                       $sub_date = $dd.'-FEB-'.$yy;
                    }
					else if($month == '3')
                    {
                       $sub_date = $dd.'-MAR-'.$yy;
                    }
					else if($month == '4')
                    {
                     	$sub_date = $dd.'-APR-'.$yy;
                    }
					else if($month == '5')
                    {
                       $sub_date = $dd.'-MAY-'.$yy;
                    }
					else if($month == '6')
                    {
                       $sub_date = $dd.'-JUN-'.$yy;
                    }
					else if($month == '7')
                    {
                       $sub_date = $dd.'-JUL-'.$yy;
                    }
					  else if($month == '8')
                    {
                      $sub_date = $dd.'-AUG-'.$yy;
                    }
					else if($month == '9')
                    {
                       $sub_date = $dd.'-SEP-'.$yy;
                    }
					else if($month == '10')
                    {
                       $sub_date = $dd.'-OCT-'.$yy;
                    }
					else if($month == '11')
                    {
                       $sub_date = $dd.'-NOV-'.$yy;
                    }
					else
                    {
                       $sub_date = $dd.'-DEC-'.$yy;
                    }
					
					return $sub_date;
  } 
  
  //it return state name...
  function get_state($id=0)
  {
  	$result = $this->db->query("select state_name from tbl_states where state_id=".$id);
	$state = $result->result_array();
	return $state[0]['state_name'];
  }
  
  //it returns area name...
  function get_area($id=0)
  {
  	$result = $this->db->query("select area_name from tbl_area where area_id=".$id);
	$area = $result->result_array();
	return $area[0]['area_name'];
  }
  
  //it returns city name....
  function get_city($id=0)
  {
  	$result = $this->db->query("select city_name from tbl_city where city_id=".$id);
	$city = $result->result_array();
	return $city[0]['city_name'];
  }
  //to return poi and poa details
  function get_poitype($id=0)
  {
  	$result = $this->db->query("select poi_desc from tbl_poidetails where poi_id=".$id);
	$poi = $result->result_array();
	list($short,$desc) = explode ('-', $poi[0]['poi_desc']);
	return $short;
  }
  
  
  
  function read_xlsdata($file_name)
  {
    require_once 'excel_reader2.php';
    $xls = new Spreadsheet_Excel_Reader('./cafuploads/'.$file_name);
    list($file) = explode(".",$file_name);
    
    $this->db->set('batch_filename',$file);    
            
    $this->db->insert('tbl_batchfile');
    
        //this is to get file id....
    $result = $this->db->query("select file_id from tbl_batchfile where batch_filename='".$file."'");
    
    $id = $result->result_array();
        
    $file_id =$id[0]['file_id'];
    
    $batch_no = $xls->dump_batch();
    
    $caf_details = $xls->dump_caf();
    
    
    
    foreach($batch_no as $row)
    {
      
      $this->db->set('batch_num',$row['b'] );
      $this->db->set('total_batch_records',$row['t'] );
      $this->db->set('file_id',$file_id );
      
      list($month,$date,$year) = explode('-',$row['d']);
                    if($month == 'Jan')
                    {
                       $act_date = $year.'-01-'.$date;
                    }
                    if($month == 'Feb')
                    {
                       $act_date = $year.'-02-'.$date;
                    }
                    if($month == 'Mar')
                    {
                       $act_date = $year.'-03-'.$date;
                    }
                    if($month == 'Apr')
                    {
                       $act_date = $year.'-04-'.$date;
                    }
                    if($month == 'May')
                    {
                       $act_date = $year.'-05-'.$date;
                    }
                    if($month == 'Jun')
                    {
                       $act_date = $year.'-06-'.$date;
                    }
                    if($month == 'Jul')
                    {
                       $act_date = $year.'-07-'.$date;
                    }
                    if($month == 'Aug')
                    {
                       $act_date = $year.'-08-'.$date;
                    }
                    if($month == 'Sep')
                    {
                       $act_date = $year.'-09-'.$date;
                    }
                    if($month == 'Oct')
                    {
                       $act_date = $year.'-10-'.$date;
                    }
                    if($month == 'Nov')
                    {
                       $act_date = $year.'-11-'.$date;
                    }
                    if($month == 'Dec')
                    {
                       $act_date = $year.'-12-'.$date;
                    }
      
                $this->db->set('recp_date',$act_date);
                $this->db->insert('tbl_batchdetails');
                
               
     
     }

    foreach($caf_details as $row)
    {
      
        $this->db->set('batch_number',$row['b']);
        $this->db->set('sl_num',$row['sl_num']);
        $this->db->set('batch_mobilenum',$row['mobile']);
        $this->db->set('sim_num',$row['sim_num']);
        if(!$row['date'])
        {
          $act_date='0000-00-00';
        }
        else
        {
        list($month,$date,$year) = explode('-',$row['date']);
                    if($month == 'Jan')
                    {
                       $act_date = $year.'-01-'.$date;
                    }
                    if($month == 'Feb')
                    {
                       $act_date = $year.'-02-'.$date;
                    }
                    if($month == 'Mar')
                    {
                       $act_date = $year.'-03-'.$date;
                    }
                    if($month == 'Apr')
                    {
                       $act_date = $year.'-04-'.$date;
                    }
                    if($month == 'May')
                    {
                       $act_date = $year.'-05-'.$date;
                    }
                    if($month == 'Jun')
                    {
                       $act_date = $year.'-06-'.$date;
                    }
                    if($month == 'Jul')
                    {
                       $act_date = $year.'-07-'.$date;
                    }
                    if($month == 'Aug')
                    {
                       $act_date = $year.'-08-'.$date;
                    }
                    if($month == 'Sep')
                    {
                       $act_date = $year.'-09-'.$date;
                    }
                    if($month == 'Oct')
                    {
                       $act_date = $year.'-10-'.$date;
                    }
                    if($month == 'Nov')
                    {
                       $act_date = $year.'-11-'.$date;
                    }
                    if($month == 'Dec')
                    {
                       $act_date = $year.'-12-'.$date;
                    }
      
        }              
        
        $this->db->set('activation_date',$act_date);
        $this->db->set('distributor_name',$row['dist']);
        $this->db->set('poa',$row['poa']);
        $this->db->set('poi',$row['poi']);
        $this->db->set('status',$row['status']);
        $this->db->set('received_date',date("Y-m-d"));    
            
        $this->db->insert('tbl_batchtracker');
        
        
    }

    redirect('idea_home/admin_dashboard');
    
    
  }
  /*
//to dump excel sheet....temporialy

	function dump_country()
	{
		 require_once 'excel_reader2.php';
		 $xls = new Spreadsheet_Excel_Reader('./cafuploads/countryname.xls');
		 
		   $batch_no = $xls->dump_batch();
		   foreach($batch_no as $rr)
		   {
			$this->db->set('country_name',$rr['b']);
			$this->db->insert('tbl_country');
			
		   }
		   
	}
  */
  function check_dup_file()
  {
        list($file) = explode('.',$_POST['file_name']);
        $result = $this->db->query('SELECT count(file_id) FROM tbl_batchfile where batch_filename="'.$file.'"');
     //   echo 'SELECT count(file_id) FROM tbl_batchfile where batch_filename="'.$file.'"';
        //return $result;
 
  }
  
  function get_batchbydate()
  {
    list($dd1,$mm1,$yy1) = explode('/',$_POST['start']);
    $from = $yy1.'-'.$mm1.'-'.$dd1;
     list($dd2,$mm2,$yy2) = explode('/',$_POST['end']);
     $to = $yy2.'-'.$mm2.'-'.$dd2;
   // echo "select * from tbl_batchdetails where recp_date between '".$from."' and '".$to."'";exit;
    $result = $this->db->query("select * from tbl_batchdetails where (recp_date between '".$from."' and '".$to."') and (qc_status = 1) and (export_status = 0)");
    return $result;
  }
  
  function get_batchall()
  {
     list($dd1,$mm1,$yy1) = explode('/',$_POST['start']);
    $from = $yy1.'-'.$mm1.'-'.$dd1;
     list($dd2,$mm2,$yy2) = explode('/',$_POST['end']);
     $to = $yy2.'-'.$mm2.'-'.$dd2;
   // echo "select * from tbl_batchdetails where recp_date between '".$from."' and '".$to."'";exit;
    $result = $this->db->query("select * from tbl_batchdetails where (recp_date between '".$from."' and '".$to."') and (qc_status = 1) ");
    return $result;
  }
  
}
